package jea.machine.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import jea.machine.transverseprofileviewer.PanelProfileViewerBySPSLocation;
import jea.machine.transverseprofileviewer.PanelProfileViewerByType;
import models.ModelLayoutTable;

public class DBConnect 
{
	private Connection conn = null;
	private ResultSet resultSet = null;
	private Statement statement = null;
	
	private Vector<String> shortNames = new Vector<String>();
	private PanelProfileViewerByType panelProfileByType;
	
	private Vector<String> spsLocations = new Vector<String>();
	private PanelProfileViewerBySPSLocation panelProfileSPSLocation;
	
	public DBConnect()
	{
		connect();
	}
	
    public DBConnect(PanelProfileViewerByType panelProfile)
    {
    	this.panelProfileByType = panelProfile;
    	connect();
    }
    
    public DBConnect(PanelProfileViewerBySPSLocation panelProfile)
    {
    	this.panelProfileSPSLocation = panelProfile;
    	connect();
    }
    
    // 'ELEMENTS' RECUPERE TOUS LES TYPES DE CHAMBRE A VIDE
    public Vector<String> getShortNames()
    {	
    	try
    	{ 
    	 statement = conn.createStatement();
    	 resultSet = statement.executeQuery("SELECT * FROM ELEMENTS");
    	  while(resultSet.next())
    	  {	  
    	   String sn = resultSet.getString("SHORTNAME");
    	   shortNames.add(sn);
    	   //System.out.println("SHORTNAME: "+sn);
    	  }
    	}
    	catch(Exception e)
    	{ System.out.println(e.getMessage()); }
    	//System.out.println("get shortnames done");
     return shortNames;
    }
    
    // 'ELEMENTS' RECHERCHE PAR TYPE DE CHAMBRE A VIDE
    public VacuumProfile getProfileByShortName(String shortName)
    {
    	VacuumProfile vacProfile = null;
    	try
    	{
   	     statement = conn.createStatement();
   	     System.out.println("SELECT * FROM ELEMENTS WHERE SHORTNAME LIKE '"+shortName+"'");
	     resultSet = statement.executeQuery("SELECT * FROM ELEMENTS WHERE SHORTNAME LIKE '"+shortName+"'");
	     int idprofile = resultSet.getInt("IDPROFILE"); // renommer en IDPROFILE
	     String profileDescription = resultSet.getString("FULLNAME");
	     String profileDrawingRef = resultSet.getString("IDDRAWING");
	     
	     resultSet = statement.executeQuery("SELECT * FROM PROFILESVALUES WHERE IDPROFILE="+idprofile+"");
	     vacProfile = new VacuumProfile(shortName,profileDescription,profileDrawingRef,"");
	     while ( resultSet.next())
	     {
	    	 System.out.println("# POINT ="+resultSet.getInt("POINT"));
	    	 System.out.println("valeur X="+resultSet.getDouble("PROFILEX"));
	    	 System.out.println("valeur Y="+resultSet.getDouble("PROFILEY"));
	    	 vacProfile.addPoint(resultSet.getInt("POINT"), resultSet.getDouble("PROFILEX"), resultSet.getDouble("PROFILEY"));
	     }
	     
	     // PEINT LE PROFILE SELECTIONNE
	     panelProfileByType.paintProfile(vacProfile);     
    	} 
    	catch(Exception e)	{ e.printStackTrace(); 	}

    	return vacProfile;
    }
   
    
    // 'PROFILESBYSPSLOCATION' RECUPERE TOUTES LES SPS LOCATIONS
    public Vector<String> getSPSLocations()
    {
    	try
    	{ 
    	 statement = conn.createStatement();
    	 resultSet = statement.executeQuery("SELECT * FROM PROFILESBYSPSLOCATION");
    	  while(resultSet.next())
    	  {	  
    	   String spsloc = resultSet.getString("SPSLOCATION");
    	   spsLocations.add(spsloc);
    	  }
    	}
    	catch(Exception e)
    	{ System.out.println(e.getMessage()); }
     return spsLocations;      	
    }
    
    // 'PROFILESBYSPSLOCATION' RECHERCHE PAR SPS LOCATION
    public VacuumProfile getProfileBySPSLocation(String spsLoc)
    {
    	VacuumProfile vacProfile = null;
    	try
    	{
   	     statement = conn.createStatement();
   	      System.out.println("SELECT * FROM PROFILESBYSPSLOCATION WHERE SPSLOCATION LIKE '"+spsLoc+"'");
   	     resultSet = statement.executeQuery("SELECT * FROM PROFILESBYSPSLOCATION WHERE SPSLOCATION LIKE '"+spsLoc+"'");
   	     String shortName = resultSet.getString("PROFILETYPE");
   	     
   	     if ( shortName == "" || shortName == null )
   	     {
   	    	 System.out.println("NOT IN DB !");
   	     }
   	     
   	      System.out.println("SELECT * FROM ELEMENTS WHERE SHORTNAME LIKE '"+shortName+"'");
	     resultSet = statement.executeQuery("SELECT * FROM ELEMENTS WHERE SHORTNAME LIKE '"+shortName+"'");
	     
	     if (resultSet.isClosed() || resultSet.getRow() < 1)
   	     {
   	    	 System.out.println("NOT YET IN DB !");
   		     // PEINT LE PROFILE SELECTIONNE
   		     panelProfileSPSLocation.paintProfile(null);
   		     return null;
   	     }
	     
	     int idprofile = resultSet.getInt("IDPROFILE"); // renommer en IDPROFILE
	     String profileDescription = resultSet.getString("FULLNAME");
	     String profileDrawingRef = resultSet.getString("IDDRAWING");
	     
	     resultSet = statement.executeQuery("SELECT * FROM PROFILESVALUES WHERE IDPROFILE="+idprofile+"");
	     vacProfile = new VacuumProfile(shortName,profileDescription,profileDrawingRef,"");
	     while ( resultSet.next())
	     {
	    	 System.out.println("# POINT ="+resultSet.getInt("POINT"));
	    	 System.out.println("valeur X="+resultSet.getDouble("PROFILEX"));
	    	 System.out.println("valeur Y="+resultSet.getDouble("PROFILEY"));
	    	 vacProfile.addPoint(resultSet.getInt("POINT"), resultSet.getDouble("PROFILEX"), resultSet.getDouble("PROFILEY"));
	     }
	     
	     // PEINT LE PROFILE SELECTIONNE
	     panelProfileSPSLocation.paintProfile(vacProfile);     
    	} 
    	catch(Exception e)	{ e.printStackTrace(); 	}
    	
    	return vacProfile;
    }
    
   // 'LAYOUT' EXTRAIT LES 'SPSPOSITION' A PARTIR D'UNE 'SPSPOSITION'
   public Object[] getLayoutFromSPSLocation(int fromSPSPosition)
   {
	Vector<String> fromPos = new Vector<String>();   
   	try
	{
	     statement = conn.createStatement();
	     String query = "SELECT * FROM LAYOUT  WHERE SPSPOSITION >= " + fromSPSPosition;
	     System.out.println(query);
	     resultSet = statement.executeQuery(query);
	     while ( resultSet.next())
	     {
	    	 String position = resultSet.getString("SPSPOSITION");
	    	 String length = resultSet.getString("LENGTH");	    	 
	    	 String type = resultSet.getString("TYPE");
	    	 String description = resultSet.getString("DESCRIPTION");
	    	 String comboItem = position + " [" + length  + "m] - " + type + " - " + description; 
	    	 fromPos.add(comboItem);
	     } 
	}
	 catch(Exception e)	{ e.printStackTrace(); 	}
	return fromPos.toArray(); 	   
   }
   // 'LAYOUT' EXTRAIT LES 'SPSLOCATION' JUSQU'A UNE 'SPSLOCATION'   
   public Object[] getLayoutToSPSPosition(int toSPSPosition)
   {
	Vector<String> toPos = new Vector<String>();   
   	try
	{
	     statement = conn.createStatement();
	     String query = "SELECT * FROM LAYOUT  WHERE SPSPOSITION <= " + toSPSPosition;
	     System.out.println(query);
	     resultSet = statement.executeQuery(query);
	     while ( resultSet.next())
	     {
	    	 String position = resultSet.getString("SPSPOSITION");
	    	 String lenght = resultSet.getString("LENGTH");
	    	 String type = resultSet.getString("TYPE");
	    	 String description = resultSet.getString("DESCRIPTION");
	    	 String comboItem = position + " [" + lenght  + "m] - " + type + " - " + description; 
	    	 toPos.add(comboItem);
	     }
	}
	 catch(Exception e)	{ e.printStackTrace(); 	}
	return toPos.toArray(); 	   
   }   
    // 'LAYOUT' RECHERCHE PAR SPS LOCATION
    public ModelLayoutTable getLayoutBetweenSPSPosition(int fromSPSPosition, int toSPSPosition)
    {
    	Vector<String> columns = new Vector<String>();
        String colNames[] = null;   
    	Object data[][] = null;
  	     String synthese = "";
    	try
    	{
   	     statement = conn.createStatement();
   	     String query = "PRAGMA table_info(LAYOUT)"; // Recupere le nom des colonnes
   	     resultSet = statement.executeQuery(query);
   	     while ( resultSet.next() )
   	     {
   	//       System.out.println("COL="+ resultSet.getString("NAME"));
   	       columns.add(resultSet.getString("NAME"));
   	     }
   	     colNames = new String[columns.size()];
   	     colNames = columns.toArray(colNames);
   	     
   	     query = "SELECT * FROM LAYOUT WHERE SPSPOSITION >= " + fromSPSPosition + " AND SPSPOSITION <= " + toSPSPosition;
   	     System.out.println(query);
   	     resultSet = statement.executeQuery(query);

   	     int rows=0;
   	     while( resultSet.next() )
   	    	 rows++;
   	     System.out.println("# OF ROW = "+rows);
   	     
   	     resultSet = statement.executeQuery(query);
   	     data = new Object[rows][columns.size()];  
   	     int r = 0;   //  r= 0 sera la ligne de synthese
   	     double longueurTotale = 0.0;
   	     double widthMin = Double.MAX_VALUE, widthMax = Double.MIN_VALUE, heigthMin = Double.MAX_VALUE,  heigthMax = Double.MIN_VALUE;
   	     String     widthMinPosition = "", widthMaxPosition = "", heigthMaxPosition = "", heigthMinPosition = "";
	     while ( resultSet.next())
	     {
	    	 for(int i = 0; i < columns.size(); i++)
	    	 {
	    		 data[r][i] = resultSet.getString(colNames[i]);
	    		 // Somme des longueurs
	    		 if ( colNames[i].equalsIgnoreCase("LENGTH") )
	    		 	 longueurTotale += Double.valueOf((String)data[r][i]);
	    		 // Min Max Width
	    		 if ( colNames[i].equalsIgnoreCase("WIDTH") )
	    		 {
	    			 if ( Double.valueOf((String)data[r][i]) < widthMin )
	    			 {	 
	    				 widthMin = Double.valueOf((String)data[r][i]);
	    				 widthMinPosition = resultSet.getString("SPSPOSITION");
	    			 }	 
	    			 if ( Double.valueOf((String)data[r][i]) > widthMax )
	    			 {	 
	    				 widthMax = Double.valueOf((String)data[r][i]);
	    				 widthMaxPosition = resultSet.getString("SPSPOSITION");
	    			 }	 
	    		 }
	    		 // Min Max Heigth
	    		 if ( colNames[i].equalsIgnoreCase("HEIGHT") )
	    		 {
	    			 if ( Double.valueOf((String)data[r][i]) < heigthMin )
	    			 {	 
	    				 heigthMin = Double.valueOf((String)data[r][i]);
	    				 heigthMinPosition = resultSet.getString("SPSPOSITION");
	    			 }	 
	    			 if ( Double.valueOf((String)data[r][i]) > heigthMax )
	    			 {	 
	    				 heigthMax = Double.valueOf((String)data[r][i]);
	    				 heigthMaxPosition = resultSet.getString("SPSPOSITION");
	    			 }	 
	    		 }	    		 
	    		 //System.out.println("DATA="+data[r][i].toString());
	    	 }
	    	 r++;
	     }	 
	     // LIGNE DE SYNTHESE
	     /*
    	 for(int i = 0; i < columns.size(); i++)
    	 {
    		     		 // Somme des longueurs
    		 if ( colNames[i].equalsIgnoreCase("LENGTH") )
    		 	 data[0][i] = "Total "+longueurTotale;
    		 // Min Max Width
    		 else if ( colNames[i].equalsIgnoreCase("WIDTH") )
                 data[0][i] = "Min " + widthMin + "\r Max " + widthMax;
    		 // Min Max Heigth
    		 else if ( colNames[i].equalsIgnoreCase("HEIGHT") )
    			 data[0][i] =  "Min " + heigthMin + ", Max " + heigthMax;
    		 else
    			 data[0][i] = "-";
    	 }	     
    	 data[0][0] = rows + " VACUUM ITEMS";
    	 */	    
	     
	     synthese = rows + " ELEMENTS, Total Length = " + longueurTotale + " [m] , MIN W. = " 
	     + widthMin + " [m] @" + widthMinPosition + ", MAX W. = " + widthMax + " [m] @ " + widthMaxPosition + ", MIN H. = " + heigthMin + " [m] @ " + heigthMinPosition + ",  MAX H. = " + heigthMax + " [m] @ " + heigthMaxPosition;
	     
    	}
    	catch(Exception e)	{  e.printStackTrace(); 	}
 	
    	String frameTitle = fromSPSPosition + " To " +toSPSPosition; 
        ModelLayoutTable lt= new ModelLayoutTable(synthese, frameTitle, colNames, data);
   
     return lt;    	
    }
    
    // DB CONNECTION 
    public void connect()
    {
      	try
    	{
    	 Class.forName("org.sqlite.JDBC");
    	 //conn = DriverManager.getConnection("jdbc:sqlite:/user/jaxensal/apertures.db");
    	 conn = DriverManager.getConnection("jdbc:sqlite:/home/jea/workspace/apertures.db");
    	 //conn = DriverManager.getConnection("jdbc:sqlite:D:\\cernjars\\apertures.db");
    	 //conn = DriverManager.getConnection("jdbc:sqlite:D:\\cernjars\\aperturesprod.db");
    	}
      	catch(Exception e)  { e.printStackTrace(); }
     }
    
    public void close()
    {
		try
		{ 
		 resultSet.close();
		 statement.close();
		 conn.close();
		}
		catch(Exception e) { e.printStackTrace(); }    	
    }

    /*
	public static void main(String[] args) 
    {
    	DBConnect db = new DBConnect();
    	db.getShortNames();
    }
    */
}
